﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OfficeOpenXml;
using OfficeOpenXml.Style;

public partial class admincp_dshocsinh : AdminPage
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Page_PreRender(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.FillDataToDropDownListNamHoc();
            this.FillDataToDropDownListLopHoc();
        }
    }

    private void FillDataToDropDownListNamHoc()
    {
        ddlNamHoc.DataSource = db.NamHocs.Where(nh => nh.TrangThai == true);
        ddlNamHoc.DataTextField = "TenNamHoc";
        ddlNamHoc.DataValueField = "MaNamHoc";
        ddlNamHoc.DataBind();
        ddlNamHoc.Items.Insert(0, "Chọn năm học");
    }

    private void FillDataToDropDownListLopHoc()
    {
        ddlLopHoc.DataSource = db.LopHocs.Where(lh => lh.MaNamHoc == ddlNamHoc.SelectedValue);
        ddlLopHoc.DataTextField = "TenLop";
        ddlLopHoc.DataValueField = "MaLop";
        ddlLopHoc.DataBind();
        ddlLopHoc.Items.Insert(0, "Chọn lớp học");
    }
    protected void ddlNamHoc_SelectedIndexChanged(object sender, EventArgs e)
    {
        this.FillDataToDropDownListLopHoc();
    }

    protected void XacNhan(object sender, EventArgs e)
    {
        if (ddlNamHoc.SelectedIndex < 1 || ddlLopHoc.SelectedIndex < 1)
        {
            //Báo lỗi dữ liệu đầu vào không hợp lệ
            ClientScriptManager script = Page.ClientScript;
            if (!script.IsClientScriptBlockRegistered(this.GetType(), "Alert"))
                script.RegisterClientScriptBlock(this.GetType(), "Alert", "<script type=text/javascript>alert('Xin vui lòng chọn đầy đủ thông tin')</script>");
        }
        else
            this.FillDataToListView();
    }

    private void FillDataToListView()
    {
        var hocsinh = (from hs in db.PhanLopHocSinhs
                       where hs.MaLop == ddlLopHoc.SelectedValue
                       select hs)
                       .AsEnumerable()
                       .Select((_hs, index) => new
                       {
                           SoThuTu = _hs.SoThuTu,
                           MaHocSinh = _hs.HocSinh.MaHocSinh,
                           TenHocSinh = _hs.HocSinh.TenHocSinh,
                           DienThoai = _hs.HocSinh.DienThoai,
                           Email = _hs.HocSinh.Email,
                           TrangThai = _hs.HocSinh.TrangThai
                       }).OrderBy(_hs=>_hs.SoThuTu);
        lvHocSinh.DataSource = hocsinh;
        lvHocSinh.DataBind();
    }

    protected void XuatExcel(object sender, EventArgs e)
    {
        if (lvHocSinh.Items.Count > 0)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet ws = package.Workbook.Worksheets.Add("DSHOCSINH");

                /****************************************************************
                *      BẮT ĐẦU ĐỊNH DẠNG PHẦN HEADER CHO FILE EXCEL             *
                ****************************************************************/
                ws.Cells["A1"].Value = "Trường THPT Trường Chinh - " + ddlNamHoc.SelectedItem.Text;
                ws.Cells["A1"].Style.Font.Bold = true;
                ws.Cells["A1:E1"].Merge = true;
                ws.Cells["A2"].Value = "LỚP: " + ddlLopHoc.SelectedItem.Text;
                ws.Cells["A2"].Style.Font.Bold = true;
                ws.Cells["A2"].Style.Font.Size = 16;
                ws.Cells["A2:E2"].Merge = true;
                ws.Cells["A3"].Value = "STT";
                ws.Cells["A3"].Style.Font.Bold = true;
                ws.Cells["B3"].Value = "MSHS";
                ws.Cells["B3"].Style.Font.Bold = true;
                ws.Cells["C3"].Value = "Họ và Tên";
                ws.Cells["C3"].Style.Font.Bold = true;
                ws.Cells["D3"].Value = "Điện thoại";
                ws.Cells["D3"].Style.Font.Bold = true;
                ws.Cells["E3"].Value = "Email";
                ws.Cells["E3"].Style.Font.Bold = true;
                ws.Cells["A3:E3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                ws.Cells["A3:E3"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                var borderA3E3 = ws.Cells["A3:E3"].Style.Border;
                borderA3E3.Bottom.Style = borderA3E3.Left.Style = borderA3E3.Right.Style = borderA3E3.Top.Style = ExcelBorderStyle.Thin;
                

                /****************************************************************
                *      KẾT THÚC ĐỊNH DẠNG PHẦN HEADER CHO FILE EXCEL           *
                ****************************************************************/

                /****************************************************************
                *      BẮT ĐẦU TỔNG HỢP DỮ LIỆU CHO BÁO CÁO XUẤT RA EXCEL      *
                ****************************************************************/
                var hocsinh = (from hs in db.PhanLopHocSinhs
                               where hs.MaLop == ddlLopHoc.SelectedValue
                               select hs)
                       .AsEnumerable()
                       .Select((_hs, index) => new
                       {
                           SoThuTu = _hs.SoThuTu,
                           PhuHieu = _hs.SoPhuHieu,
                           MaHocSinh = _hs.HocSinh.MaHocSinh,
                           TenHocSinh = _hs.HocSinh.TenHocSinh,
                           DienThoai = _hs.HocSinh.DienThoai,
                           Email = _hs.HocSinh.Email,
                           TrangThai = _hs.HocSinh.TrangThai
                       }).OrderBy(_hs => _hs.SoThuTu);
                int DongBatDau = 4;
                foreach (var hs in hocsinh)
                {
                    ws.Cells[DongBatDau, 1].Value = hs.SoThuTu;
                    ws.Cells[DongBatDau, 2].Value = hs.PhuHieu;
                    ws.Cells[DongBatDau, 3].Value = hs.TenHocSinh;
                    ws.Cells[DongBatDau, 4].Value = hs.DienThoai;
                    ws.Cells[DongBatDau, 5].Value = hs.Email;
                    var border = ws.Cells[DongBatDau, 1, DongBatDau, 5].Style.Border;
                    border.Bottom.Style = border.Left.Style = border.Right.Style = border.Top.Style = ExcelBorderStyle.Thin;
                    DongBatDau++;
                }

                /****************************************************************
                *                             XUẤT RA EXCEL                     *
                ****************************************************************/
                Response.Clear();
                Response.BinaryWrite(package.GetAsByteArray());
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;  filename=nhapdiem.xlsx");
                Response.End();
                //package.SaveAs(Response.OutputStream);
                //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                //Response.AddHeader("content-disposition", "attachment;  filename=dshocsinh.xlsx");
            }
        }
    }
}